package dto;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import db.MySql;
import vo.messageVO;
import vo.workVO;

public class workDTO {

	private workVO wvo;
	private Connection conn = null;
	
	
	//페이지에 관련된 변수
		int listSize = 3;  //페이지당 표시되는 행수
		int blockSize = 5; //한블럭당 표시되는 페이지 수
		int totSize = 0;   //전체 게시물의 갯수
		int totPage = 0;   //전체 페이지 수
		int totBlock = 0;  //전체 블럭 수
		int nowPage = 0;   //현재 페이지
		int nowBlock = 0;  //현재 블럭
		int startNo = 0;   //리스트 목록의 시작 위치
		int endNo = 0;     //리스트 목록의 끝 위치
		int startPage = 0; //현재 블럭에서 표시되는 시작 블럭 번호
		int endPage = 0;   //현재 블럭에서 표시되는 끝 블럭 번호
		
	public workDTO() {
		// TODO Auto-generated constructor stub
	}

	public workDTO(workVO wvo) {
		this.wvo = wvo;
		this.nowPage = wvo.getNowPage();
		this.nowBlock = wvo.getNowBlock();
		
		conn = new MySql().getConn();
	}
	
	public void list_size_set(int list_size){
		this.listSize = list_size;
	}
	
	
	public void page() {	// 페이지 구하기
		
		String sql  = "SELECT count(*) cnt FROM semyadmin.work WHERE m_id = ? ";
		
		try {
			
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, wvo.getM_id());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				System.out.println("rs.getInt('cnt') : " +  rs.getInt("cnt"));
				totSize = rs.getInt("cnt");

				totPage = (int) Math.ceil(totSize * 1.0 / listSize);
				totBlock = (int) Math.ceil(totPage * 1.0 / blockSize);
				nowBlock = (int) Math.ceil(nowPage * 1.0 / blockSize);

				endPage = nowBlock * blockSize;
				startPage = endPage - blockSize + 1;

				endNo = nowPage * listSize;
				startNo = endNo - listSize + 1;

				if (endPage > totPage)
					endPage = totPage;
				if (endNo > totSize)
					endNo = totSize;
			}
			
//			System.out.println("totSize=" + totSize);
//			
//			System.out.println("totPage=" + totPage);
//			System.out.println("totBlock=" + totBlock);
//			System.out.println("nowBlock=" + nowBlock);
//			
//			System.out.println("totBlock=" + totBlock);
//			System.out.println("nowBlock=" + nowBlock);
//			
//			System.out.println("endPage=" + endPage);
//			System.out.println("startPage=" + startPage);
//			
//			System.out.println("endNo=" + endNo);
//			System.out.println("startNo=" + startNo);
			
			rs.close();
			pst.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public ArrayList<workVO> list() { // list 처리 + 검색
		ArrayList<workVO> al = new ArrayList<workVO>();
		
		page();
		
		String sql = " SELECT * "
				+ " FROM  ( "
				+ " SELECT *, date_format(A.w_end_day ,'%Y년%m월%d일') AS end_time, "
				+ " date_format(A.w_start_day ,'%Y년%m월%d일') AS start_time "
				+ " FROM ( "
				+ " SELECT w.*, @RNUM := @RNUM+1 AS RNUM "
				+ " FROM (SELECT @RNUM := 0) tmp INNER JOIN semyadmin.work w "
				+ " WHERE  w.m_id = ? AND w.w_user_team = ? "
				+ " )A "
				+ " )B "
				+ " WHERE B.RNUM BETWEEN ? AND ? "
				+ " ORDER BY B.w_end_day ASC";
		
		try {
			
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1,  wvo.getM_id() );
			pst.setInt(2,  wvo.getUser_team() );
			pst.setInt(3, startNo);		// 
			pst.setInt(4, endNo);		// 
			
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				workVO v = new workVO();
				
				v.setW_number(rs.getInt("w_number"));
				v.setW_start_day(rs.getString("start_time"));
				v.setW_end_day(rs.getString("end_time"));
				v.setW_title(rs.getString("w_title"));
				v.setW_type(rs.getInt("w_type"));
				v.setUser_team(rs.getInt("w_user_team"));
				v.setW_context(rs.getString("w_context"));
				v.setM_id(rs.getString("m_id"));
				
				
				al.add(v);
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		
		return al;
	}

	
	
	public workVO view() { 	// view : 선택한 글 보기 
		workVO v = new workVO();
		String sql = "	SELECT *, "
				+ "			date_format(w_end_day ,'%Y년%m월%d일') AS end_time,  "
				+ "			date_format(w_start_day ,'%Y년%m월%d일') AS start_time "
				+ "			FROM semyadmin.work "
				+ "			WHERE w_number = ? ";

		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			System.out.println("\n" + "view의 b_number : " + wvo.getW_number());
			pst.setInt(1, wvo.getW_number());
			ResultSet rs = pst.executeQuery();

			while (rs.next()) {
				
				v.setW_number(rs.getInt("w_number"));
				v.setW_start_day(rs.getString("start_time"));
				v.setW_end_day(rs.getString("end_time"));
				v.setW_title(rs.getString("w_title"));
				v.setW_type(rs.getInt("w_type"));
				v.setUser_team(rs.getInt("w_user_team"));
				v.setW_context(rs.getString("w_context"));
				v.setM_id(rs.getString("m_id"));
				
				System.out.println("메시지 뷰 들어옴");
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return v;
	}
	
	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}
	
}
